package starkuz.lg.dao.impl.sites;

import starkuz.lg.dao.ifaces.sites.LaunSiteDao;
import starkuz.lg.model.SiteStatuses;
import starkuz.lg.model.sites.LaunSite;
import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Repository
public class LaunSiteDaoImpl implements LaunSiteDao {

    private static final String SELECT_ALL_LAUN_SITES =
            "select * from laun_sites";
    private static final String SELECT_ALL_ACTUAL_LAUN_SITES_BY_USER_ID =
            "select * from laun_sites where ownerId = ? and not status = 'deleted'";
    private static final String SELECT_ALL_LAUN_SITES_BY_USER_ID =
            "select * from laun_sites where ownerId = ?";
    private static final String SELECT_LAUN_SITE_BY_ID = "select * from laun_sites where id = ?";
    private static final String SELECT_ALL_LAUN_SITES_BY_USER_ID_AND_STATUS =
            "select * from laun_sites where ownerId = ? and status = ?";
    private static final String SELECT_ALL_LAUN_SITES_BY_STATUS =
            "select * from laun_sites where status = ?";
    private static final String SELECT_ALL_LAUN_SITES_BY_USER_ID_AND_STATUS_AND_DATE_BETWEEN =
            "select * from laun_sites where ownerId = ? and status = ? and dateMade between ? and ?";

    private static final String DELETE_LAUN_SITE_BY_ID = "delete from laun_sites where id = ?";
    private static final String UPDATE_LAUN_SITE_TO_DELETED =
            "update laun_sites set status = 'deleted' where id = ?";
    private static final String INSERT_LAUN_SITE =
            "insert into laun_sites (ownerId, idTemplate, titleTag, keywordTag, descripTag, mainHeader, briefDef," +
                    " inputsCode, companyName, mySite, dopText, action, formCode, statCode, marketType," +
                    " status,bgImg,  dateMade, dateLastChange)" +
                    " values (?, ?, ?, ?, ? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,?)";
    private static final String SET_LAUN_SITE_STATUS_BY_ID = "update laun_sites set status = ? where id = ?";
    private static final String UPDATE_LAUN_SITE =
            "update laun_sites set ownerId = ?, idTemplate=?, titleTag = ?, keywordTag = ?, descripTag = ?," +
                    "mainHeader = ?, briefDef = ?, inputsCode = ?, companyName = ?, mySite = ?," +
                    "dopText = ?, action = ?, formCode = ?, statCode = ?, marketType = ?," +
                    "bgImg = ?, where id = ?";

    @Autowired
    private BasicDataSource ds;

    public void setDs(BasicDataSource ds) {
        this.ds = ds;
    }

    @Override
    public List<LaunSite> getAllLaunSites() {
        List<LaunSite> sites = new ArrayList<LaunSite>();
        Connection con = null;
        ResultSet res = null;
        try {
            con = ds.getConnection();
            Statement st = con.createStatement();
            res = st.executeQuery(SELECT_ALL_LAUN_SITES);
            sites = resultSetToLaunSitesList(res, sites);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                res.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return sites;
    }

    @Override
    public LaunSite findLaunSiteById(Integer id) {
        LaunSite launSite = new LaunSite();
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_LAUN_SITE_BY_ID);
            psql.setInt(1, id);
            launSite = resultSetToLaunSite(psql.executeQuery(), launSite);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return launSite;
    }

    @Override
    public List<LaunSite> getAllLaunSitesForUser(Integer userId) {
        List<LaunSite> sites = new ArrayList<LaunSite>();
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_ALL_LAUN_SITES_BY_USER_ID);
            psql.setInt(1, userId);
            sites = resultSetToLaunSitesList(psql.executeQuery(), sites);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return sites;
    }

    @Override
    public List<LaunSite> getAllActualLaunSitesForUser(Integer userId) {
        List<LaunSite> sites = new ArrayList<LaunSite>();
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_ALL_ACTUAL_LAUN_SITES_BY_USER_ID);
            psql.setInt(1, userId);
            sites = resultSetToLaunSitesList(psql.executeQuery(), sites);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return sites;
    }

    @Override
    public List<LaunSite> getAllLaunSitesForUserByStatus(Integer userId, String status) {
        List<LaunSite> sites = new ArrayList<LaunSite>();
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_ALL_LAUN_SITES_BY_USER_ID_AND_STATUS);
            psql.setInt(1, userId);
            psql.setString(2, status);
            sites = resultSetToLaunSitesList(psql.executeQuery(), sites);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return sites;
    }

    @Override
    public List<LaunSite> getAllSitesByStatus(String status) {
        List<LaunSite> sites = new ArrayList<LaunSite>();
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_ALL_LAUN_SITES_BY_STATUS);
            psql.setString(1, status);
            sites = resultSetToLaunSitesList(psql.executeQuery(), sites);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return sites;
    }

    @Override
    public void setStatusBySiteId(Integer siteId, String status) {
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SET_LAUN_SITE_STATUS_BY_ID);
            psql.setString(1, status);
            psql.setInt(2, siteId);
            psql.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    @Override
    public void addLaunSite(LaunSite launSite) {
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(INSERT_LAUN_SITE);
            psql.setInt(1, launSite.getOwnerId());
            psql.setInt(2, launSite.getIdTemplate());
            psql.setString(3, launSite.getTitleTag());
            psql.setString(4, launSite.getKeywordTag());
            psql.setString(5, launSite.getDescripTag());
            psql.setString(6, launSite.getMainHeader());
            psql.setString(7, launSite.getBriefDef());
            psql.setString(8, launSite.getInputsCode());
            psql.setString(9, launSite.getCompanyName());
            psql.setString(10, launSite.getMySite());
            psql.setString(11, launSite.getDopText());
            psql.setString(12, launSite.getAction());
            psql.setString(13, launSite.getFormCode());
            psql.setString(14, launSite.getStatCode());
            psql.setString(15, launSite.getMarketType());
            psql.setString(16, SiteStatuses.SITE_STATUS_ACTIVE); //при создании сайт активен
            psql.setString(17, launSite.getBgImg());
            psql.setDate(18, new Date(new java.util.Date().getTime())); //дата в момент создания сайта
            psql.setDate(19, new Date(new java.util.Date().getTime()));
            psql.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void deleteLaunSiteById(Integer launSiteId) {
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con  = ds.getConnection();
            psql = con.prepareStatement(UPDATE_LAUN_SITE_TO_DELETED);
            psql.setInt(1, launSiteId);
            psql.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void updateLaunSite(LaunSite launSite) {
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(UPDATE_LAUN_SITE);
            psql.setInt(1, launSite.getOwnerId());
            psql.setInt(2, launSite.getIdTemplate());
            psql.setString(3, launSite.getTitleTag());
            psql.setString(4, launSite.getKeywordTag());
            psql.setString(5, launSite.getDescripTag());
            psql.setString(6, launSite.getMainHeader());
            psql.setString(7, launSite.getBriefDef());
            psql.setString(8, launSite.getInputsCode());
            psql.setString(9, launSite.getCompanyName());
            psql.setString(10, launSite.getMySite());
            psql.setString(11, launSite.getDopText());
            psql.setString(12, launSite.getAction());
            psql.setString(13, launSite.getFormCode());
            psql.setString(14, launSite.getStatCode());
            psql.setString(15, launSite.getMarketType());
            psql.setString(16, launSite.getBgImg());
            psql.setInt(17, launSite.getId());
            psql.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private LaunSite resultSetToLaunSite(ResultSet rs, LaunSite launSite) throws SQLException {
        while (rs.next()) {
            launSite = new LaunSite();
            launSite.setId(rs.getInt("id"));
            launSite.setIdTemplate(rs.getInt("idTemplate"));
            launSite.setOwnerId(rs.getInt("ownerId"));
            launSite.setTitleTag(rs.getString("titleTag"));
            launSite.setKeywordTag(rs.getString("keywordTag"));
            launSite.setDescripTag(rs.getString("descripTag"));
            launSite.setMainHeader(rs.getString("mainHeader"));
            launSite.setBriefDef(rs.getString("briefDef"));
            launSite.setInputsCode(rs.getString("inputsCode"));
            launSite.setCompanyName(rs.getString("companyName"));
            launSite.setMySite(rs.getString("mySite"));
            launSite.setDopText(rs.getString("dopText"));
            launSite.setAction(rs.getString("action"));
            launSite.setFormCode(rs.getString("formCode"));
            launSite.setStatCode(rs.getString("statCode"));
            launSite.setMarketType(rs.getString("marketType"));
            launSite.setStatus(rs.getString("status"));
            launSite.setBgImg(rs.getString("bgImg"));
            launSite.setDateMade(rs.getDate("dateMade"));
            launSite.setDateLastChange(rs.getDate("dateLastChange"));
        }
        return launSite;
    }

    private List<LaunSite> resultSetToLaunSitesList(ResultSet rs, List<LaunSite> sites) throws SQLException {
        while (rs.next()) {
            LaunSite launSite = new LaunSite();
            launSite.setId(rs.getInt("id"));
            launSite.setOwnerId(rs.getInt("ownerId"));
            launSite.setIdTemplate(rs.getInt("idTemplate"));
            launSite.setTitleTag(rs.getString("titleTag"));
            launSite.setKeywordTag(rs.getString("keywordTag"));
            launSite.setDescripTag(rs.getString("descripTag"));
            launSite.setMainHeader(rs.getString("mainHeader"));
            launSite.setBriefDef(rs.getString("briefDef"));
            launSite.setInputsCode(rs.getString("inputsCode"));
            launSite.setCompanyName(rs.getString("companyName"));
            launSite.setMySite(rs.getString("mySite"));
            launSite.setDopText(rs.getString("dopText"));
            launSite.setAction(rs.getString("action"));
            launSite.setFormCode(rs.getString("formCode"));
            launSite.setStatCode(rs.getString("statCode"));
            launSite.setMarketType(rs.getString("marketType"));
            launSite.setStatus(rs.getString("status"));
            launSite.setBgImg(rs.getString("bgImg"));
            launSite.setDateMade(rs.getDate("dateMade"));
            launSite.setDateMade(rs.getDate("dateMade"));
            launSite.setDateLastChange(rs.getDate("dateLastChange"));

            sites.add(launSite);
        }
        return sites;
    }
}
